package com.luoke.util.myjdbc;

import cn.hutool.core.util.StrUtil;
import cn.hutool.log.StaticLog;
import com.luoke.constant.DatabaseConstants;
import com.luoke.pojo.Ignore;
import com.luoke.util.myjdbc.util.DBUtil;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class SQLExecute<T> {

    private static Connection connection;

    //    private Class<T> clazz;
    static {
        {
            try {
                connection = DriverManager.getConnection(DatabaseConstants.DB_URL, DatabaseConstants.DB_USER, DatabaseConstants.DB_PASSWORD);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public List<T> executeQueryWrapper(QueryWrapper<T> queryWrapper) {
        List<T> result = new ArrayList<>();
        StaticLog.debug(queryWrapper.getSql().toString());
        try (PreparedStatement pstmt = fillSQL(queryWrapper.getSql().toString(), queryWrapper.getParams(), false)) {
            ResultSet rs = pstmt.executeQuery();
            Class<T> clazz = queryWrapper.getClazz();
            while (rs.next()) {
                T obj = createInstance(clazz);
                fillObject(rs, obj); // 调用填充方法
                result.add(obj);
            }
        } catch (SQLException e) {
            StaticLog.error(e.getMessage());
            throw new RuntimeException(e);
        }
        return result;
    }


    public Integer executeUpdateWrapper(UpdateWrapper<T> updateWrapper) {
        StaticLog.debug(updateWrapper.getSql().toString());
        try (PreparedStatement pstmt = fillSQL(updateWrapper.getSql().toString(), updateWrapper.getParams(), false)) {
            return pstmt.executeUpdate();//返回受影响的行数
        } catch (Exception e) {
            StaticLog.error(e.getMessage());
            throw new RuntimeException(e);
        }
    }

    public Integer executeUpdateById(T entity) {
        UpdateWrapper<T> updateWrapper = new UpdateWrapper<>();
        Class<?> clazz = entity.getClass();
        updateWrapper.update(DBUtil.getTableName(clazz));
        boolean isSet = false;
        Object idValue = null;
        try {
            // 获取类的 Class 对象
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                //因为实体类中有序列化所需要标记的字段，这里排除掉
                if (Modifier.isStatic(field.getModifiers())) {  //该方法用来判断是否是静态成员变量
                    continue; // 跳过静态字段
                }
                field.setAccessible(true); // 设置private字段可访问
                String columnName = StrUtil.toUnderlineCase(field.getName()); // 转换为下划线命名
                Object fieldValue = field.get(entity); // 获取字段值
                if ("id".equals(columnName)) {
                    idValue = fieldValue;
                    continue;
                }
                if (!isSet && fieldValue != null) {
                    updateWrapper.set(columnName, String.valueOf(fieldValue));
                    isSet = true;
                } else if (fieldValue != null) {
                    updateWrapper.comma(columnName, String.valueOf(fieldValue));
                }
            }
            updateWrapper.where("id", String.valueOf(idValue));
            StaticLog.debug(updateWrapper.getSql().toString());
        } catch (Exception e) {
            StaticLog.error(e.getMessage());
            throw new RuntimeException(e);
        }

        try (PreparedStatement pstmt = fillSQL(updateWrapper.getSql().toString(), updateWrapper.getParams(), false)) {
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            StaticLog.error(e.getMessage());
            throw new RuntimeException(e);
        }
    }


    public T executeSelectOneById(Long id, Class<T> clazz) {
        QueryWrapper<T> queryWrapper = new QueryWrapper<>();
        List<T> result = new ArrayList<>();
        queryWrapper.select(SQLEnum.ASTERISK_SIGN.getValue()).from(clazz).where("id", String.valueOf(id));
        StaticLog.debug(queryWrapper.getSql().toString());
        try (PreparedStatement pstmt = fillSQL(queryWrapper.getSql().toString(), queryWrapper.getParams(), false)) {
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                T obj = createInstance(clazz);
                fillObject(rs, obj); // 调用填充方法
                result.add(obj);
            }

        } catch (Exception e) {
            StaticLog.error(e.getMessage());
            throw new RuntimeException(e);
        }
        if (result.size() > 0) {
            return result.get(0);
        }
        return null;
    }

    public Long executeInsert(T entity) {
        //insert into pet (name,hp,mp,create_time,level) values (?,?,?,?,?)
        Class<?> clazz = entity.getClass();
        String sql = null;
        String tableName = DBUtil.getTableName(clazz);
        List<String> columns = new ArrayList<>();
        List<String> queSignList = new ArrayList<>();
        List<String> values = new ArrayList<>();
        try {
            // 获取类的 Class 对象
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                //因为实体类中有序列化所需要标记的字段，这里排除掉
                if (Modifier.isStatic(field.getModifiers())) {  //该方法用来判断是否是静态成员变量
                    continue; // 跳过静态字段
                }
                field.setAccessible(true); // 设置private字段可访问
                String columnName = StrUtil.toUnderlineCase(field.getName()); // 转换为下划线命名
                Object fieldValue = field.get(entity); // 获取字段值
                if (fieldValue == null) {
                    continue; //值为空则不插入
                }
                values.add(String.valueOf(fieldValue));
                columns.add(columnName);
                queSignList.add(SQLEnum.QUE_SIGN.getValue());
            }
            sql = SQLEnum.INSERT_INTO.getValue() + StrUtil.SPACE
                    + tableName + StrUtil.SPACE + SQLEnum.PARENTHESIS_START.getValue()
                    + StrUtil.join(",", columns)
                    + SQLEnum.PARENTHESIS_END.getValue() + StrUtil.SPACE
                    + SQLEnum.VALUES + StrUtil.SPACE + SQLEnum.PARENTHESIS_START.getValue()
                    + StrUtil.join(",", queSignList) + SQLEnum.PARENTHESIS_END.getValue();

        } catch (Exception e) {
            StaticLog.error(e.getMessage());
            throw new RuntimeException(e);
        }
        try (PreparedStatement pstmt = fillSQL(sql, values, true)) {
            ResultSet rs = null;
            pstmt.executeUpdate();
            rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            StaticLog.error(e.getMessage());
            throw new RuntimeException(e);
        }
        //执行插入
        return -1L;
    }

    public List<Long> executeInsertBatch(List<T> entityList) {
        List<Long>list=new ArrayList<>();
        for (T entity : entityList) {
            Long id = this.executeInsert(entity);
            list.add(id);
        }
        return list;
    }

    public Integer executeDeleteById(Long id, Class<T> clazz) {
        String sql = SQLEnum.DELETE.getValue() + StrUtil.SPACE + SQLEnum.FROM.getValue()
                + StrUtil.SPACE + DBUtil.getTableName(clazz) + StrUtil.SPACE
                + SQLEnum.WHERE.getValue() + StrUtil.SPACE + "id" + SQLEnum.EQ_SIGN.getValue()
                + SQLEnum.QUE_SIGN.getValue();
        try (PreparedStatement pstmt = fillSQL(sql, Arrays.asList(String.valueOf(id)), false)) {
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            StaticLog.error(e.getMessage());
            throw new RuntimeException(e);
        }
    }

    public void executeDeleteByIds(List<Long> ids, Class<T> clazz) {
        for (Long id : ids) {
            this.executeDeleteById(id, clazz);
        }
    }

    public Integer executeApply(String sql, SQLEnum sqlEnum) {
        switch (sqlEnum) {
            case UPDATE:
            case DELETE:
            case INSERT:
                return executeApplyByUpdate(sql);
            default:
                throw new RuntimeException("不支持的sql类型");
        }
    }

    public PreparedStatement executeApply(String sql, SQLEnum sqlEnum, Class<T> clazz) {
        switch (sqlEnum) {
            case SELECT:
                return executeApplyByQuery(sql, clazz);
            default:
                throw new RuntimeException("不支持的sql类型");
        }
    }

    private Integer executeApplyByUpdate(String sql) {
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            StaticLog.error(e.getMessage());
            throw new RuntimeException(e);
        }
    }

    private PreparedStatement executeApplyByQuery(String sql, Class<T> clazz) {
        List<Object> result = new ArrayList<>();
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            StaticLog.info(sql);
            return pstmt;
//            while (rs.next()) {
//                Object obj = createInstance(clazz);
//                fillObject(rs, obj); // 调用填充方法
//                result.add(obj);
//            }
        } catch (SQLException e) {
            StaticLog.error(e.getMessage());
            throw new RuntimeException(e);
        }
    }

    private void fillObject(ResultSet rs, Object obj) {
        Field[] fields = obj.getClass().getDeclaredFields(); //这里通过反射拿到字段名
//        ResultSetMetaData rsmd = rs.getMetaData();
//        int columnCount = rsmd.getColumnCount();
//        System.out.println("列数：" + columnCount);
        for (Field field : fields) {
            //如果字段上有 @Ignore 注解，则跳过对该字段的处理
            if (field.isAnnotationPresent(Ignore.class)) {
                continue;
            }
            //因为实体类中有序列化所需要标记的字段，这里排除掉
            if (Modifier.isStatic(field.getModifiers())) {  //该方法用来判断是否是静态成员变量
                continue; // 跳过静态字段
            }

            field.setAccessible(true); // 设置private字段可访问

            String columnName = StrUtil.toUnderlineCase(field.getName()); // 转换为下划线命名
//            System.out.println(columnName);
            Object value = null;
            try {
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    String dbColumnName = rs.getMetaData().getColumnName(i);
                    if (columnName.equals(dbColumnName)) {
                        if (columnName.equals("create_time") || columnName.equals("last_login") || columnName.equals("update_time")) {
                            value = rs.getObject(columnName, LocalDateTime.class);
                        } else {
                            value = rs.getObject(columnName);
                        }
                        field.set(obj, value);
                    }
                }

            } catch (Exception e) {
                StaticLog.error("fillObject 异常 {}", e.getMessage());
                throw new RuntimeException(e);
            }
        }

    }

    public PreparedStatement fillSQL(String sql, List<String> params, Boolean isReturnGeneratedKeys) {
        PreparedStatement pstmt = null;
        try {
            pstmt = connection.prepareStatement(sql, isReturnGeneratedKeys ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);//设置主键回显
            //拼接参数
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }
        } catch (SQLException e) {
            StaticLog.error("fillSQL 异常");
            throw new RuntimeException(e);
        }
        return pstmt;
    }


    private <T> T createInstance(Class<T> clazz) {
        if (clazz == null) {
            StaticLog.error("createInstance 异常");
            throw new IllegalArgumentException("Class cannot be null");
        }
        try {
            return clazz.getDeclaredConstructor().newInstance();
        } catch (Exception e) {
            StaticLog.error("createInstance 异常");
            throw new RuntimeException(e);
        }
    }
}
